package gov.pbc.nn.kjgl.fxq.riskAssess.view;

import java.beans.PropertyDescriptor;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import com.bstek.bdf2.core.business.IUser;
import com.bstek.bdf2.core.context.ContextHolder;
import com.bstek.bdf2.core.exception.NoneLoginException;
import com.bstek.bdf2.core.orm.hibernate.ISessionCallback;
import com.bstek.bdf2.core.service.IDeptService;
import com.bstek.dorado.annotation.DataProvider;
import com.bstek.dorado.annotation.DataResolver;
import com.bstek.dorado.annotation.Expose;
import com.bstek.dorado.data.entity.EntityState;
import com.bstek.dorado.data.entity.EntityUtils;
import com.bstek.dorado.data.provider.Criteria;
import com.bstek.dorado.data.provider.Page;
import com.bstek.dorado.uploader.UploadFile;
import com.bstek.dorado.uploader.annotation.FileResolver;

import gov.pbc.nn.core.bdf.entity.Dept;
import gov.pbc.nn.core.dao.CommonHibernateDao;
import gov.pbc.nn.kjgl.fxq.attachment.AttachmentEntity;
import gov.pbc.nn.kjgl.fxq.attachment.GBKOrder;
import gov.pbc.nn.kjgl.fxq.riskAssess.entity.RiskAssessContentEntity;
import gov.pbc.nn.kjgl.fxq.riskAssess.entity.RiskAssessListEntity;
import gov.pbc.nn.kjgl.fxq.workDocument.view.WorkDocumentPR;

@Component(RiskAssessContenPR.BEAN_ID)
public class RiskAssessContenPR extends CommonHibernateDao {
	public static final String BEAN_ID = "riskAssessContenPR";

	@Autowired
	@Qualifier(IDeptService.BEAN_ID)
	private IDeptService deptService;

	@Resource(name = WorkDocumentPR.BEAN_ID)
	private WorkDocumentPR workDocumentPR;

	@DataProvider
	public void loadRiskAssessLists(Page<RiskAssessListEntity> page, Criteria criteria) throws Exception {
		IUser user = ContextHolder.getLoginUser();
		if (user == null)
			throw new NoneLoginException("请先登录！");
		if (user.getDepts() == null || user.getDepts().isEmpty())
			throw new NoneLoginException("您未设置部门！");
		Dept dept = (Dept) user.getDepts().get(0);
		DetachedCriteria detachedCriteria = this.buildDetachedCriteria(criteria, RiskAssessListEntity.class);
		detachedCriteria.add(Restrictions.eq("deptId", dept.getCDept().getId()));
		detachedCriteria.add(Restrictions.eq("invalid", false));
		if (criteria != null && criteria.getOrders() != null && !criteria.getOrders().isEmpty()) {
			com.bstek.dorado.data.provider.Order order = criteria.getOrders().get(0);
			if (order.isDesc()) {
				detachedCriteria.addOrder(Order.desc(order.getProperty()));
			} else {
				detachedCriteria.addOrder(Order.asc(order.getProperty()));
			}
		} else {
			detachedCriteria.addOrder(Order.desc("createDate"));
		}
		this.pagingQuery(page, detachedCriteria);
	}

	@DataProvider
	public Collection<?> loadRiskContents(String listId) throws Exception {
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(RiskAssessContentEntity.class);
		detachedCriteria.add(Restrictions.eq("invalid", false));
		detachedCriteria.add(Restrictions.eq("listId", listId));
		detachedCriteria.addOrder(GBKOrder.asc("level1Code"));
		detachedCriteria.addOrder(GBKOrder.asc("level1Content"));
		detachedCriteria.addOrder(GBKOrder.asc("level2Code"));
		detachedCriteria.addOrder(GBKOrder.asc("level2Content"));
		return this.query(detachedCriteria);
	}

	@DataResolver
	@Transactional
	public void saveRiskAssessLists(List<RiskAssessListEntity> riskAssessLists) throws Exception {
		IUser user = ContextHolder.getLoginUser();
		if (user == null)
			throw new NoneLoginException("请先登录！");
		if (user.getDepts() == null || user.getDepts().isEmpty())
			throw new NoneLoginException("您未设置部门！");
		Dept dept = (Dept) user.getDepts().get(0);
		for (RiskAssessListEntity riskAssessList : riskAssessLists) {
			EntityState state = EntityUtils.getState(riskAssessList);
			RiskAssessListEntity riskAssessListEntity = EntityUtils.toEntity(riskAssessList);
			riskAssessList.setState(10);
			if (state.equals(EntityState.NEW)) {
				riskAssessList.setDeptId(dept.getCDept().getId());
				riskAssessList.setDeptName(dept.getCDept().getName());
				this.save(riskAssessList);
			} else if (state.equals(EntityState.MODIFIED)) {
				this.update(riskAssessList);
			} else if (state.equals(EntityState.DELETED)) {
				this.delete(riskAssessList);
			}

			List<AttachmentEntity> attachments = EntityUtils.getValue(riskAssessListEntity, "attachments");
			if (attachments != null) {
				this.update(riskAssessList);
				for (AttachmentEntity attachmentEntity : attachments) {
					this.workDocumentPR.saveEntity(attachmentEntity);
				}
			}
			
			List<RiskAssessContentEntity> contents = EntityUtils.getValue(riskAssessListEntity, "contents");
			if (contents != null) {
				this.update(riskAssessList);
				for (RiskAssessContentEntity content : contents) {
					this.workDocumentPR.saveEntity(content);
				}
			}
		}
	}

	@Expose
	@DataResolver
	@Transactional
	public void submitRiskAssessList(RiskAssessListEntity riskAssessList) throws Exception {
		if (riskAssessList.getState() != 20) {
			riskAssessList.setState(20);
			riskAssessList.setSubmitDate(new Date());
			this.update(riskAssessList);
		}
	}

	@DataResolver
	@Transactional
	public void getContentByIndexTemplate(final RiskAssessListEntity riskAssessList)throws Exception {
		final IUser user = ContextHolder.getLoginUser();
		if (user == null)
			throw new NoneLoginException("请先登录！");
		if (user.getDepts() == null || user.getDepts().isEmpty())
			throw new NoneLoginException("您未设置部门！");
		RiskAssessListEntity riskAssessListEntity = EntityUtils.toEntity(riskAssessList);
		final String templateId=EntityUtils.getValue(riskAssessListEntity, "templateId");
		this.doInHibernateSession(new ISessionCallback<Void>() {

			@Override
			public Void doInSession(Session session) {
				// TODO Auto-generated method stub
				Transaction t = session.beginTransaction();
				try {
					Query queryDelete= session.createQuery("update RiskAssessContentEntity set invalid=true where listId=:listId ");
					queryDelete.setString("listId", riskAssessList.getId());
					queryDelete.executeUpdate();
					String sql=" INSERT INTO t_fxq_risk_assess_content_enttiy(ID_,LIST_ID_,LEVEL1_CODE_,LEVEL1_CONTENT_,LEVEL2_CODE_,LEVEL2_CONTENT_,SCORE_STANDARD_,SCORE_,SELF_SCORE_, "
							 + " SELF_POINTS_,FIRST_SCORE_,FIRST_POINTS_,FINAL_SCORE_,FINAL_POINTS_ ,CREATE_DATE_,CREATE_USER_,CREATE_USER_CNAME_,INVALID_) "
							 + " SELECT REPLACE(UUID(),'-','') as ID_,:listId as LIST_ID_,LEVEL1_CODE_,LEVEL1_CONTENT_,LEVEL2_CODE_,LEVEL2_CONTENT_,SCORE_STANDARD_,SCORE_,0 as SELF_SCORE_,0 as SELF_POINTS_,"
							 + " 0 as FIRST_SCORE_,0 as FIRST_POINTS_ ,0 as FINAL_SCORE_,0 as FINAL_POINTS_ ,NOW() as CREATE_DATE_,:username as CREATE_USER_,:cname as CREATE_USER_CNAME_,0 as INVALID_"
							 + " FROM t_fxq_risk_assess_index_enttiy where TEMPLATE_ID_=:templateId and INVALID_=0 and INDEX_TYPE_='二级指标' ";
					Query queryInsert= session.createSQLQuery(sql);
					queryInsert.setString("listId", riskAssessList.getId());
					queryInsert.setString("username", user.getUsername());
					queryInsert.setString("cname", user.getCname());
					queryInsert.setString("templateId", templateId);
					queryInsert.executeUpdate();
					riskAssessList.setState(10);
					session.update(riskAssessList);
					t.commit();
				} catch (Exception e) {
					// TODO: handle exception
					t.rollback();
					session.clear();
					e.printStackTrace();
				}
				
				return null;
			}
		});
	}
	
	@FileResolver
	public Map<String, Object> importData(UploadFile file, Map<String, Object> parameter) throws Exception {
		final String listId = (String) parameter.get("listId");
		Map<String, Object> data = new HashMap<String, Object>();
		IUser user = ContextHolder.getLoginUser();
		if (user == null)
			throw new NoneLoginException("请先登录！");
		if (user.getDepts() == null || user.getDepts().isEmpty())
			throw new NoneLoginException("您未设置部门！");
		final Map<String, String> nameMap = new HashMap<String, String>();		
		nameMap.put("一级指标编号", "level1Code");
		nameMap.put("一级指标内容", "level1Content");
		nameMap.put("二级指标编号", "level2Code");
		nameMap.put("二级指标内容", "level2Content");
		nameMap.put("自评得分", "selfScore");
		nameMap.put("自评扣分", "selfPoints");
		nameMap.put("自评理由", "selfReason");
		nameMap.put("初评得分", "firstScore");
		nameMap.put("初评扣分", "firstPoints");
		nameMap.put("初评理由", "firstReason");
		nameMap.put("终评得分", "finalScore");
		nameMap.put("终评扣分", "finalPoints");
		nameMap.put("终评理由", "finalReason");
		nameMap.put("发现问题", "problem");
		nameMap.put("简要描述", "remark");
		Set<String> labelSet = new HashSet<String>();
		for(Entry<String, String> entry : nameMap.entrySet()){
			labelSet.add(entry.getKey());
		}
		Workbook workbook;
		if (file.getFileName().toUpperCase().endsWith(".XLS")) {
			workbook = new HSSFWorkbook(file.getInputStream());
		} else if (file.getFileName().toUpperCase().endsWith(".XLSX")) {
			workbook = new XSSFWorkbook(file.getInputStream());
		} else {
			data.put("success", false);
			data.put("errorInfo", "导入文件格式错误！");
			return data;
		}
		final Sheet sheet = workbook.getSheetAt(0);
		if (sheet == null) {
			data.put("success", false);
			data.put("errorInfo", "导入文件不存在Sheet！");
			return data;
		}
		final Map<String, Integer> indexMap = new HashMap<String, Integer>();
		Row labelRow = sheet.getRow(0);
		if (labelRow == null) {
			data.put("success", false);
			data.put("errorInfo", "导入表格数据为空！");
			return data;
		}
		Iterator<Cell> iterCell = labelRow.cellIterator();
		while (iterCell.hasNext()) {
			Cell cell = iterCell.next();
			cell.setCellType(Cell.CELL_TYPE_STRING);
			String label = cell.getStringCellValue();
			if (nameMap.containsKey(label)) {
				indexMap.put(label, cell.getColumnIndex());
				labelSet.remove(label);
			}
		}
		if (!labelSet.isEmpty()) {
			data.put("success", false);
			data.put("errorInfo", "缺少列【" + labelSet.toArray()[0] + "】");
			return data;
		}
		
		return this.doInHibernateSession(new ISessionCallback<Map<String, Object>>() {

			@Override
			public Map<String, Object> doInSession(Session session) {
				// TODO Auto-generated method stub
				
				Map<String, Object> data = new HashMap<String, Object>();
				Transaction t = session.beginTransaction();
				try {					
					int rowNum = sheet.getPhysicalNumberOfRows();				
					Query queryUpdate= session.createQuery("update RiskAssessContentEntity set selfScore=:selfScore,selfPoints=:selfPoints,selfReason=:selfReason,firstScore=:firstScore,firstPoints=:firstPoints,"
							+ " firstReason=:firstReason,finalScore=:finalScore,finalPoints =:finalPoints,finalReason=:finalReason,problem=:problem,remark=:remark where level1Code=:level1Code and "
							+ " level1Content=:level1Content and level2Code=:level2Code and level2Content=:level2Content and listId=:listId");
					queryUpdate.setString("listId", listId);
					for (int i = 1; i < rowNum; i++) {
						Row row = sheet.getRow(i);
						for(Entry<String, String> entry : nameMap.entrySet()){
							String label=entry.getKey();
							String name = entry.getValue();
							int index = indexMap.get(label);
							Cell cell = row.getCell(index);
							cell.setCellType(Cell.CELL_TYPE_STRING);
							Object value = cell.getStringCellValue();
							/*if (!name.equals("problem") && !name.equals("remark")&&StringUtils.isBlank(value.toString())) {
								data.put("success", false);
								data.put("errorInfo", "第" + (row.getRowNum() + 1) + "行第" + (index + 1) + "列,【" + label + "】不能为空!");
								t.rollback();
								session.clear();
								return data;
							}*/
							if (value == null)
								value = "";
							Class cla = new PropertyDescriptor(name, RiskAssessContentEntity.class).getPropertyType();							
							if (cla.getName().equals("int")) {
								try {
									value = Integer.parseInt(value.toString());
								} catch (Exception e) {
									data.put("success", false);
									data.put("errorInfo", "第" + (row.getRowNum() + 1) + "行第" + (index + 1) + "列,满分分值应为整数!");
									t.rollback();
									session.clear();
									return data;
								}
							}

							queryUpdate.setParameter(name, value);
						}
						queryUpdate.executeUpdate();
					}
					data.put("success", true);
					data.put("errorInfo", "");
					t.commit();
					return data;
				} catch (Exception e) {
					// TODO: handle exception
					t.rollback();
					session.clear();
					data.put("success", false);
					data.put("errorInfo", e.getMessage());
					e.printStackTrace();
					return data;
				}
			}
		});

	}
}
